package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

public class DBCustomer implements IFDBCustomer{
    private  Connection con;
    /** Creates a new instance 
     * of DBEmployee */
    public DBCustomer() {
      con = DBConnection.getInstance().getDBcon();
    }
  //Implements the methods from the interface
    // get all customer
    public ArrayList<Customer> getAllCustomers(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }
    //get one customer having the cprno
    public Customer findCustomer(String cprno, boolean retriveAssociation)
    {   
    	String wClause = "  cprno = '" + cprno + "'";
        return singleWhere(wClause, retriveAssociation);
    }
    //find one customer having the fname
    public Customer searchCustomerFname(String attValue, boolean retriveAssociation)
    {
        String wClause = "fname like '%" + attValue + "%'";
        System.out.println("SearchCustomer " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
      //find one customer having the lname
    public Customer searchCustomerLname(String attValue, boolean retriveAssociation)
    {
        String wClause = "lname = '" + attValue + "'";
        System.out.println("SearchCustomer " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
      //find one customer having the cprno
    public Customer searchCustomerCprno(String attValue, boolean retriveAssociation)
    {
        String wClause = "cprno = '" + attValue + "'";
        System.out.println("Search Customer: " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
      //find one customer having the phoneno
    public Customer searchCustomerPhoneno(String attValue, boolean retriveAssociation)
    {
    	String wClause = "phoneno = '" + attValue + "'";
    	System.out.println("Search Customer: " + wClause);
    	return singleWhere(wClause, retriveAssociation);
    }
    // find one customer having the clubID
    public Customer searchCustomerClubID(int attValue, boolean retriveAssociation)
    {
    	String wClause = "clubID = '" + attValue + "'";
    	System.out.println("SearchCustomer: " + wClause);
    	return singleWhere(wClause, retriveAssociation);
    }
     //insert a new customer 
    public void insertCustomer(Customer customer) throws SQLException
    {  
        int nextCprno = GetMax.getMaxId("Select max(cprno) from Customer");
        nextCprno = nextCprno + 1;
        System.out.println("next cprno = " +  nextCprno);
  
	   String query="INSERT INTO customer(cprno, fname, lname, phoneno, address, codezip, clubID)  VALUES('"+
	  		customer.getCprNo()   + "','"  +
		    customer.getFname()   + "','"  +
            customer.getLname()   + "','"  +
		    customer.getPhoneNo() + "','"  +
            customer.getAddress() + "','"  +
		    customer.getCity().getCodeZip() + "','"  +
            customer.getClub().getClubID()  + "')";
                     
           
      System.out.println("insert : " + query);		  					
      try{
    	  con.setAutoCommit(false);
          Statement stmt = con.createStatement();
	 	  stmt.setQueryTimeout(5);
	 	  stmt.executeUpdate(query);
	 	  stmt.close();
	 	  con.commit();
	 	  con.setAutoCommit(false);
		}
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in customer db: "+ex);
	 	  	try{
	 	  		con.rollback();
	 	  		con.setAutoCommit(true);
	 	  		System.out.println("Rolled back.");
	 	  	}catch(Exception exr){
	 	  		System.out.println("Error in rolling back: "+exr);
	 	  	}
	   }
	}
	
	public void updateCustomer(Customer customer)
	{
		Customer customerObj  = customer;
	  
		String query="UPDATE customer SET "+
		 	  "fname ='"+ customerObj.getFname()+"', "+
		 	  "lname ='"+ customerObj.getLname() + "', " +
		 	  "phoneno ='"+ customerObj.getPhoneNo() + "', " +
		 	  "address ='"+ customerObj.getAddress() + "', " +
		 	  "codezip ='"+ customerObj.getCity().getCodeZip() + "', " +
		 	  "clubID ='" + customerObj.getClub().getClubID() + "'" +
		          " WHERE cprno = '"+ customerObj.getCprNo() + "'";
                System.out.println("Update query:" + query);
  		try{
  			con.setAutoCommit(false);
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	stmt.executeUpdate(query);
	 	 	stmt.close();
	 	 	con.commit();
		 	con.setAutoCommit(false);
		}
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in customer db: "+ex);
	 	 	try{
	 	  		con.rollback();
	 	  		con.setAutoCommit(true);
	 	  		System.out.println("Rolled back.");
	 	  	}catch(Exception exr){
	 	  		System.out.println("Error in rolling back: "+exr);
	 	  	}
	  	}
	}
	
	public void deleteCustomer(Customer customer)
	{
	  
	  	String query="DELETE FROM customer WHERE cprno = '" +
				customer.getCprNo() + "'";
                System.out.println(query);
	  	try{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	stmt.executeUpdate(query);
	 	  	stmt.close();
  		}
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in customer db: "+ex);
   	        }
	}
	
	//private methods
        //michWere is used whenever we want to select more than one customer
	
	 
	private ArrayList<Customer> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Customer> list = new ArrayList<Customer>();	
		
		String query =  buildQuery(wClause);
  
  		try{ // read the customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		

			while( results.next() ){
		     	 Customer customerObj = new Customer();
			 customerObj = buildCustomer(results);	
			 if(retrieveAssociation)
             {  
             	//Add club part
             	DBClub dbClub = new DBClub();
             	Club clubObj = dbClub.findClubByID(customerObj.getClub().getClubID());
                 customerObj.setClub(clubObj);
                 
                 //Add city part
                 DBCity dbCity = new DBCity();
                 City cityObj = dbCity.findCityByPostCode(customerObj.getCity().getCodeZip());
                 customerObj.setCity(cityObj);
             }
                         list.add(customerObj);	
			}
                       
			stmt.close();
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	//Singlewhere is used when we only select one customer
	private Customer singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Customer customerObj = new Customer();
	    String query =  buildQuery(wClause);
        System.out.println(query);
		try{ // read the customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            customerObj = buildCustomer(results);
                            //association is to be build
                            stmt.close();
                            if(retrieveAssociation)
                            {  
                            	//Add club part
                            	DBClub dbClub = new DBClub();
                            	Club clubObj = dbClub.findClubByID(customerObj.getClub().getClubID());
                                customerObj.setClub(clubObj);
                                
                                //Add city part
                                DBCity dbCity = new DBCity();
                                City cityObj = dbCity.findCityByPostCode(customerObj.getCity().getCodeZip());
                                customerObj.setCity(cityObj);
                            }
			}
		
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return customerObj;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM Customer";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	//method to build an customer object
	private Customer buildCustomer(ResultSet results)
      {   Customer customerObj = new Customer();
          try{
                customerObj.setCprNo(results.getString(1));
                customerObj.setFname(results.getString(2));
                customerObj.setLname(results.getString(3));
                customerObj.setPhoneNo(results.getString(4));
	  	        customerObj.setAddress(results.getString(5));
	        	customerObj.getCity().setCodeZip(results.getInt(6));
	        	customerObj.getClub().setClubID(results.getInt(7));
         }
         catch(Exception e)
         {
             System.out.println("error in building the customer object");
         }
         return customerObj;
      }
}  